## 白名单处理数据库
import mysql.connector  # 或者 import PyMySQL  
from mysql.connector import Error  
from sshtunnel import SSHTunnelForwarder
import pandas as pd
import logging  
  
# 创建一个logger
logging.basicConfig(level=logging.INFO, format='%(asctime)s - %(levelname)s - %(message)s')
logger = logging.getLogger(__name__)
logger.setLevel(logging.DEBUG)  #


 
# 设置数据库服务器的参数（假设数据库运行在远程服务器上）
db_host = '110.188.24.186'
db_port = 13306
db_user = 'root'
db_password = 'HYzh248713@.'
db_name = 'hyzh-server'
 

# 如果中文乱码，尝试使用以下方法
def read_excel_with_encoding(file_path, sheet_name, encoding='utf-8'):
    # 使用 openpyxl 引擎读取 Excel 文件
    excel_file = pd.ExcelFile(file_path, engine='openpyxl')
    
    # 读取特定的工作表
    df = excel_file.parse(sheet_name)
    
    # 将 DataFrame 中的字符串列转换为 Unicode 编码
    for col in df.columns:
        if df[col].dtype == 'object':
            df[col] = df[col].astype(str).apply(lambda x: x.encode(encoding).decode(encoding))
    
    return df
    
# 读取 Excel 文件
df = read_excel_with_encoding('眉山高新白名单综合.xlsx', sheet_name='Sheet1')

# 连接到MySQL数据库  
try:  
    connection = mysql.connector.connect(
        host='110.188.24.186',
        port=13306,
        database='hyzh-server',
        user='root',
        password='HYzh248713@.',
        connect_timeout=10  # 将10替换为所需的秒数
    )
    print("连接成功")
    if connection.is_connected():  
        print('Connected to MySQL database 33333')  
  
        cursor = connection.cursor()
        #将 DataFrame 写入 MySQL 数据库
        for row in df.itertuples(index=False):

            if row.carnumb == '':
                continue

            # 执行 SQL 查询
            sql_query = """  
                SELECT id,car_numb 
                    from t_gate_reservation_white 
                    where 
                    deleted = 0
                    and park_id = '387c0863-cfcf-4865-84df-886d9f3584de'
                    and car_numb=%s
                    limit 1;
                    """
            cursor.execute(sql_query,(row.carnumb,))
            # 获取所有查询结果
            results = cursor.fetchone()
            if results:
                print(results)

            sql_co_info = """
                    SELECT id, company_name
                    FROM co_info
                    WHERE park_id = '387c0863-cfcf-4865-84df-886d9f3584de'
                    AND company_name LIKE CONCAT('%', %s, '%')
                    LIMIT 1
                """
            # 执行 SQL 查询
            cursor.execute(sql_co_info, (row.company,))  # 注意这里的参数传递方式
            co_results = cursor.fetchone()
            if co_results:
                sql_updata = """  
                    UPDATE t_gate_reservation_white_copy1 set company_id=%s, company_name=%s  where car_numb = %s and id = %s;
                        """
                # 执行 SQL 查询
                cursor.execute(sql_co_info, (co_results[0],row.company,row.carnumb,results[0],))  # 注意这里的参数传递方式
                # 提交事务
                connection.commit()


        else:  
            print('No devices found in t_gate_device')  
  
except Error as e:  
    print(f"Error connecting to MySQL222: {e}")  
finally:  
    cursor.close()  
    connection.close()  
    print('MySQL connection is closed1111')
